package net.media.datafw.database;

import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import net.media.datafw.DataException;
import net.media.datafw.database.DataBaseConnection;
import net.media.datafw.database.model.DataBaseField;
import net.media.datafw.database.model.Parameter;
import net.media.datafw.database.model.Query;

public class QueryExecutor {

	Logger logger = LoggerFactory.getLogger(getClass());

	private Object execute(DataBaseConnection connection, Query query, Parameter[] inputParameters, DataBaseField[] fields, Object object, Class<?> clazz, boolean isSingle) throws DataBaseException {
		Object toRet = null;
		int maxTries = 3;
		for (int i = 0; i < maxTries; i++) {
			try {
				PreparedStatement stmt = connection.getStatement(query);
				setParameters(stmt, inputParameters);
				toRet = executeQuery(query, stmt, fields, object, clazz, isSingle);
				connection.incrementQueriesExecuted();
				break;
			} catch (Exception se) {
				if (connection.getQueriesExecuted() == 0 && i < (maxTries - 1) && connection.isConnectionClosed(se.getMessage())) {
					logger.warn(DataBaseException.PUBSUB_ERR_DB_CXN_RETRY + "A " + i, se);
					connection.reOpen();
				} else if (connection.getQueriesExecuted() > 0 || i == (maxTries - 1) || se.getMessage() == null || (!(se instanceof com.mysql.jdbc.CommunicationsException) && !(se instanceof java.net.SocketException) && !(se instanceof java.io.IOException) && (se.getMessage().indexOf(DataBaseException.PUBSUB_ERR_DB_CXN_CLOSED) == -1) && (se.getMessage().indexOf(DataBaseException.PUBSUB_ERR_DB_IO_EXCEPTION) == -1) && (se.getMessage().indexOf(DataBaseException.PUBSUB_ERR_DB_STMT_CLOSED) == -1))) {
					if (se instanceof DataBaseException) {
						throw (DataBaseException) se;
					} else {
						throw new DataBaseException(DataBaseException.ERR_UNKNOWN, se);
					}
				} else {
					logger.warn(DataBaseException.PUBSUB_ERR_DB_CXN_CLOSED_RETRYING + "B " + i, se);
					connection.reOpen();
				}
			}
		}
		return toRet;
	}

	private void setParameters(PreparedStatement stmt, Parameter[] inputParameters) throws DataBaseException {
		if (inputParameters != null) {
			for (int i = 0; i < inputParameters.length; i++) {
				setParameter(stmt, i + 1, inputParameters[i]);
			}
		}
	}

	private Object executeQuery(Query query, PreparedStatement stmt, DataBaseField[] fields, Object object, Class<?> clazz, boolean isSingle) throws DataBaseException {
		Object toRet = null;
		long startTime = System.currentTimeMillis();
		try {
			boolean fRet = false;
			fRet = stmt.execute();
			int updateCount = stmt.getUpdateCount();

			ResultSet rs = null;

			if (query.isGetGeneratedKeys()) {
				rs = stmt.getGeneratedKeys();
			} else if (fRet) {
				rs = stmt.getResultSet();
			}

			if (rs != null) {
				if (isSingle) {
					toRet = setResult(rs, fields, object);
				} else {
					toRet = setResult(rs, fields, clazz);
				}
				rs.close();
				rs = null;
			} else if (!fRet) {
				if (isSingle && updateCount == 1) {
					toRet = setResult(updateCount, fields, object);
				} else if (!isSingle && updateCount > 1) {
					toRet = setResult(updateCount, fields, object);
				} else {
					throw new DataBaseException("Query did not execute : changeCount = " + updateCount + ", Query : " + query);
				}
			} else {
				throw new DataBaseException("No Result from Query :" + query);
			}
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(DataBaseException.ERR_UNKNOWN, e);
			}
		}
		long endTime = System.currentTimeMillis();
		if ((endTime - startTime) > 1000) {
			logger.warn("SLOW QUERY: execution time = " + (endTime - startTime) + " stmt=" + stmt);
		}
		return toRet;
	}

	private Object setResult(int updateCount, DataBaseField[] fields, Object object) throws DataBaseException {
		if (fields != null && fields.length == 1) {
			try {
				fields[0].setValue(object, updateCount);
			} catch (DataException e) {
				throw new DataBaseException(e);
			}
		}
		return object;
	}

	private Object setResult(ResultSet resultSet, DataBaseField[] fields, Object object) throws DataBaseException {
		try {
			if (fields.length > 0) {
				if (resultSet.next()) {
					for (int i = 0; i < fields.length; i++) {
						setResultSet(resultSet, i + 1, fields[i], object);
					}
				}
			}
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(e);
			}
		}
		return object;
	}

	private Object setResult(ResultSet resultSet, DataBaseField[] fields, Class<?> clazz) throws DataBaseException {
		ArrayList<Object> objectAL = new ArrayList<Object>();
		try {
			if (fields.length > 0) {
				while (resultSet.next()) {
					Object object = clazz.newInstance();
					for (int i = 0; i < fields.length; i++) {
						setResultSet(resultSet, i + 1, fields[i], object);
					}
					objectAL.add(object);
				}
			}
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(e);
			}
		}
		int arraySize = objectAL.size();
		Object[] toRet = null;

		if (arraySize == 0) {
			throw new DataBaseException("No rows found in result set");
		} else {
			toRet = new Object[objectAL.size()];
			toRet = objectAL.toArray(toRet);
		}
		return toRet;
	}

	public Object doCreateOneRow(DataBaseConnection connection, Query query, Parameter[] inputParams, DataBaseField[] fields, Object object) throws DataBaseException {
		try {
			return execute(connection, query, inputParams, fields, object, null, true);
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(e);
			}
		}
	}

	public Object doLoadRows(DataBaseConnection connection, Query query, Parameter[] inputParams, DataBaseField[] fields, Object object, Class<?> clazz, boolean isSingle) throws DataBaseException {
		try {
			return execute(connection, query, inputParams, fields, object, clazz, isSingle);
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(e);
			}
		}
	}

	public Object doUpdate(DataBaseConnection connection, Query query, Parameter[] inputParams, DataBaseField[] fields, Object object, boolean isSingle) throws DataBaseException {
		try {
			return execute(connection, query, inputParams, fields, object, null, isSingle);
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(e);
			}
		}
	}

	public Object doDelete(DataBaseConnection connection, Query query, Parameter[] inputParams, DataBaseField[] fields, Object object, boolean isSingle) throws DataBaseException {
		try {
			return execute(connection, query, inputParams, fields, object, null, isSingle);
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(e);
			}
		}
	}

	private void setResultSet(ResultSet rs, int pos, DataBaseField field, Object obj) throws DataBaseException {
		try {
			Type type = field.getType();
			if ((Boolean.class).equals(type) || (boolean.class).equals(type)) {
				field.setValue(obj, rs.getBoolean(pos));
			} else if ((Byte.class).equals(type) || (byte.class).equals(type)) {
				field.setValue(obj, rs.getByte(pos));
			} else if ((Short.class).equals(type) || (Integer.class).equals(type) || (short.class).equals(type) || (int.class).equals(type)) {
				field.setValue(obj, rs.getInt(pos));
			} else if ((Long.class).equals(type) || (long.class).equals(type)) {
				field.setValue(obj, rs.getLong(pos));
			} else if ((Float.class).equals(type) || (float.class).equals(type)) {
				field.setValue(obj, rs.getFloat(pos));
			} else if ((Double.class).equals(type) || (double.class).equals(type)) {
				field.setValue(obj, rs.getDouble(pos));
			} else if ((String.class).equals(type)) {
				field.setValue(obj, (String) rs.getObject(pos));
			} else if ((BigDecimal.class).equals(type)) {
				field.setValue(obj, rs.getBigDecimal(pos));
			} else if ((Date.class).equals(type)) {
				field.setValue(obj, rs.getDate(pos));
			} else if ((Timestamp.class).equals(type)) {
				field.setValue(obj, rs.getTimestamp(pos));
			} else {
				throw new DataBaseException(DataBaseException.ERR_ILLEGAL_DATA, "Unknown data type on Field: " + field.getName() + " at position: " + pos);
			}
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(e);
			}
		}
	}

	private void setParameter(PreparedStatement stmt, int pos, Parameter parameter) throws DataBaseException {
		try {
			Type type = parameter.getType();

			if ((Boolean.class).equals(type)) {
				stmt.setBoolean(pos, (Boolean) parameter.getValue());
			} else if ((Byte.class).equals(type) || (byte.class).equals(type)) {
				stmt.setByte(pos, (Byte) parameter.getValue());
			} else if ((Short.class).equals(type) || (Integer.class).equals(type) || (short.class).equals(type) || (int.class).equals(type)) {
				stmt.setInt(pos, (Integer) parameter.getValue());
			} else if ((Long.class).equals(type) || (long.class).equals(type)) {
				stmt.setLong(pos, (Long) parameter.getValue());
			} else if ((Float.class).equals(type) || (float.class).equals(type)) {
				stmt.setFloat(pos, (Float) parameter.getValue());
			} else if ((Double.class).equals(type) || (double.class).equals(type)) {
				stmt.setDouble(pos, (Double) parameter.getValue());
			} else if ((String.class).equals(type)) {
				stmt.setObject(pos, parameter.getValue());
			} else if ((BigDecimal.class).equals(type)) {
				stmt.setBigDecimal(pos, (BigDecimal) parameter.getValue());
			} else if ((Date.class).equals(type)) {
				stmt.setDate(pos, (Date) parameter.getValue());
			} else if ((Timestamp.class).equals(type)) {
				stmt.setTimestamp(pos, (Timestamp) parameter.getValue());
			} else {
				throw new DataBaseException(DataBaseException.ERR_ILLEGAL_DATA, "Unknown data type on Field: " + parameter.getName() + " at position: " + pos);
			}
		} catch (Exception e) {
			if (e instanceof DataBaseException) {
				throw (DataBaseException) e;
			} else {
				throw new DataBaseException(e);
			}
		}
	}
}
